USE [SGLDBMP01]
GO

/*					############### 05. Table: [Ticket] ###############					*/

DECLARE @TABLE_NAME		VARCHAR(50);
SET		@TABLE_NAME		= 'Ticket';

DECLARE @TABLE_SCHEMA	VARCHAR(5);
SET		@TABLE_SCHEMA	= 'dbo';

IF (
	EXISTS (
		SELECT	[TABLE_NAME] 
		FROM	SGLDBMP01.INFORMATION_SCHEMA.TABLES
		WHERE	TABLE_NAME		= @TABLE_NAME
			AND	TABLE_SCHEMA	= @TABLE_SCHEMA
			)
	)
	-- Condition satisfied table with this name already exists in this Database
	PRINT 'Table : ' + @TABLE_NAME + ', with the name already exists in the database.'
ELSE
	BEGIN

		CREATE TABLE dbo.[Ticket] (
			  TicketId			BIGINT			NOT NULL IDENTITY(1, 1)
			, UserId			BIGINT			NOT NULL
			, CategoryId		BIGINT			NOT NULL
			, UserQuery			NVARCHAR(2000)	NULL
			, DateCreated		DATETIME		NOT NULL
			, UploadFilePath	NVARCHAR(1000)	NULL );
	
		-- Set TicketId as the Primary Key for the table dbo.[Ticket]
		ALTER TABLE dbo.[Ticket] 
			ADD CONSTRAINT PKey_Ticket_TicketId PRIMARY KEY (TicketId);			

		-- Set UserId as the Foreign Key (from dbo.User) for the table dbo.Ticket
		ALTER TABLE dbo.[Ticket] 
			ADD CONSTRAINT FKey_Ticket_UserId_UserId FOREIGN KEY (UserId)
			REFERENCES dbo.[User](UserId);

		-- Set MasterId as the Foreign Key (from dbo.User) for the table dbo.UserMaster
		ALTER TABLE dbo.[Ticket] 
			ADD CONSTRAINT FKey_Ticket_CategoryId_UserId FOREIGN KEY (CategoryId)
			REFERENCES dbo.[Category](CategoryId);

		-- By default the current DateTime will be added to the row
		ALTER TABLE dbo.[Ticket] 
			ADD CONSTRAINT DefaultConstraint_Ticket_DateCreated
			DEFAULT GETDATE() FOR DateCreated;

		IF (
			EXISTS (
				SELECT	1 
				FROM	sys.tables
				WHERE	name = @TABLE_NAME
					AND type = 'U'
					)
			)
			PRINT 'Table : ' + @TABLE_NAME + ', created successfully'
		ELSE
			PRINT 'Table : ' + @TABLE_NAME + ', creation failed, kindly check the issue'
	END

/*	Output (01): Table : Ticket, created successfully
	Output (02): Table : Ticket, with the name already exists in the database.
	Output (03): 
	Msg 2705, Level 16, State 4, Line 49
	Column names in each table must be unique. Column name 'TicketId' in table 'dbo.Ticket' is specified more than once.
*/